﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_repair_query')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_repair_query

GO


-- =============================================
-- Author:      YFC
-- Create date: 2020-07-15 23:18:59
-- Database:    [scm_main-hysj2]
-- Description: 补出勤管理-补出勤列表记录查询
-- =============================================

CREATE PROCEDURE bd_proc_repair_query
  @dateBegin DATE,
  @dateEnd  DATE,
  @depts  VARCHAR(MAX) = '',    --提交人部门权限
  @userNameOrNo VARCHAR(100) = '',  --申请人工号或姓名
  @applierNameOrNo  VARCHAR(100) = '', --提交人工号或姓名
  @dateType  INT = 1,    --1：按出勤时间查询 2：按提交时间查询
  @orderNo VARCHAR(20) = '',
  @operateId  BIGINT,   --当前登录人
  @isPage  INT = 1,
  @pageSize  INT = 50,
  @pageNumber  INT = 1,
  @rowCount  INT = 0 OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  --补出勤单号、开始日期、结束日期、总人数、补出勤事由、审批状态、最新审批人、最新审批时间、驳回原因、提交人工号、提交人姓名、提交时间

  CREATE TABLE #result
  (
    order_id BIGINT 
    , order_no VARCHAR(20) COLLATE Chinese_PRC_CI_AS   --补出勤单号
    , start_date DATE --开始日期
    , end_date DATE     --结束日期
    , people_num INT   --总人数
    , remark VARCHAR(2000) COLLATE Chinese_PRC_CI_AS --补出勤事由
    , status  INT
    , status_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS   --审批状态
    , last_approver_name  VARCHAR(100) COLLATE Chinese_PRC_CI_AS    --最新审批人
    , last_approver_time  DATETIME  --最新审批时间
    , reject_reason VARCHAR(2000) COLLATE Chinese_PRC_CI_AS   --驳回原因
    , applier_no VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --提交人工号
    , applier_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS    --提交人姓名
    , create_time DATETIME   --提交时间
  )

  DECLARE @sql VARCHAR(MAX) = '', @sql2 VARCHAR(MAX) = ''

  SELECT @sql = '
    INSERT INTO #result (order_id, order_no, start_date, end_date, people_num, remark, status, status_name, last_approver_name, last_approver_time, reject_reason, applier_no, applier_name, create_time)
    SELECT DISTINCT order_id, order_no, start_date, end_date, people_num, remark, status, status_name, last_approver_name, last_approver_time, reject_reason, applier_no, applier_name, create_time
    FROM bd_view_repair
    WHERE is_del = 0 '

  SELECT @sql2 = '
    UNION
    SELECT DISTINCT order_id, order_no, start_date, end_date, people_num, remark, status, status_name, last_approver_name, last_approver_time, reject_reason, applier_no, applier_name, create_time
    FROM bd_view_repair
    WHERE is_del = 0 AND creater_id = ' + CAST(@operateId AS VARCHAR(30))


  IF(ISNULL(@dateType, 0) = 1)
  BEGIN
    SELECT @sql += ' AND start_date >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND start_date < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
    SELECT @sql2 += ' AND start_date >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND start_date < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 
  END

  IF(ISNULL(@dateType, 0) = 2)
  BEGIN
    SELECT @sql += ' AND create_time >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND create_time < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 
    SELECT @sql2 += ' AND create_time >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND create_time < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
  END

  IF(ISNULL(@depts, '') <> '')
  BEGIN
  	SELECT @sql += ' AND applier_dept_id IN (' + @depts + ')'
  END
  
  --申请人工号、姓名
  IF(ISNULL(@userNameOrNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND (user_no LIKE ''%' + @userNameOrNo + '%'' OR user_name LIKE ''%' + @userNameOrNo + '%'')'
  	SELECT @sql2 += ' AND (user_no LIKE ''%' + @userNameOrNo + '%'' OR user_name LIKE ''%' + @userNameOrNo + '%'')'
  END

  --提交人工号、姓名
  IF(ISNULL(@applierNameOrNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND (applier_no LIKE ''%' + @applierNameOrNo + '%'' OR applier_name LIKE ''%' + @applierNameOrNo + '%'')'
  	SELECT @sql2 += ' AND (applier_no LIKE ''%' + @applierNameOrNo + '%'' OR applier_name LIKE ''%' + @applierNameOrNo + '%'')'
  END

  IF(ISNULL(@orderNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND order_no LIKE ''%' + @orderNo + '%'' '
  	SELECT @sql2 += ' AND order_no LIKE ''%' + @orderNo + '%'' '
  END
  PRINT(@sql + @sql2)
  EXEC(@sql + @sql2)

  SELECT @rowCount = COUNT(1) FROM #result 

  IF(ISNULL(@isPage, 0) = 0)
  BEGIN
  	SELECT * FROM #result ORDER BY swipe_time
  END ELSE
  BEGIN
    ;WITH result AS (
      SELECT *,row_no = ROW_NUMBER() OVER (ORDER BY order_no DESC) FROM #result 
    )
    SELECT order_id, order_no, start_date, end_date, people_num, remark, status, status_name, last_approver_name, last_approver_time, reject_reason, applier_no, applier_name, create_time, row_no
    FROM result
    WHERE row_no BETWEEN (@pageNumber - 1) * @pageSize + 1 AND @pageNumber * @pageSize	
  END

  DROP TABLE #result

  RETURN @rowCount

  SET NOCOUNT OFF;
END